CREATE OR REPLACE PACKAGE BODY pkg_brn_release_manager AS
/*****************************************************************************
* Name : pkg_brn_release_manager  
* Type : Package Specification 
* Description : Package to release the expired and used BRNs.     
*               Invoked by by DBMS scheduler  
*
* Amendment History :
*
* Version  Date         Changed By     Reason (e.g. Initial, Change Ref)
******************************************************************************
* 0.1      28/08/2013    Rajeeva Tiwari      Initial
*****************************************************************************/

   PROCEDURE brn_release
   IS
   /*****************************************************************************
   * Name : brn_release  
   * Type : PROCEDURE
   * Description : Procedure to release the expired and used BRNs.     
   * Parameters : None
   *
   * Amendment History :
   *
   * Version  Date         Changed By     Reason (e.g. Initial, Change Ref)
   ******************************************************************************
   * 0.1      28/08/2013    Rajeeva Tiwari      Initial
   *****************************************************************************/
      v_procedure_name           VARCHAR2(30 CHAR)  := 'brn_release';
      v_expiry_offset            NUMBER(10);
      v_recycle_offset           NUMBER(10);
      v_check_offset             NUMBER(10);
      v_brn_control_tmstmp       TIMESTAMP;
      v_current_tmstmp           TIMESTAMP;
      v_limit                    NUMBER(10)  := 1000;

      TYPE brn_typ  IS TABLE OF  t_basket_reference.basket_reference_number%TYPE;
      brn_arr               brn_typ := brn_typ();

      CURSOR v_brn_cur ( p_brn_control_tmstmp   TIMESTAMP
                       , p_expiry_offset        NUMBER
                       , p_recycle_offset       NUMBER
                       , p_check_offset         NUMBER
                       , p_current_tmstmp       TIMESTAMP
                       ) 
      IS
      SELECT basket_reference_number
        FROM t_basket_reference
       WHERE expiry_tmstmp >= ( p_brn_control_tmstmp - NUMTODSINTERVAL( p_recycle_offset, 'second' ) -  NUMTODSINTERVAL( p_check_offset, 'second' ) )
         AND (    ( expiry_tmstmp + NUMTODSINTERVAL( p_recycle_offset, 'second' ) ) <= p_current_tmstmp
               OR ( retrieval_tmstmp + NUMTODSINTERVAL( p_recycle_offset, 'second' ) ) <= p_current_tmstmp
             );

   BEGIN

      pkg_logger.log_progress( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_progress_message  => 'BRN release process started'
                             , p_stage => 'BRN release'
                             , p_work_identifier => NULL
                             );

      SELECT current_timestamp
        INTO v_current_tmstmp
        FROM dual;

      SELECT parameter_value
        INTO v_expiry_offset
        FROM t_parameter
       WHERE parameter_name = 'BRN_EXPIRY_OFFSET';

      SELECT parameter_value
        INTO v_recycle_offset
        FROM t_parameter
       WHERE parameter_name = 'BRN_RECYCLE_OFFSET';
     
      SELECT parameter_value
        INTO v_check_offset
        FROM t_parameter
       WHERE parameter_name = 'BRN_CHECK_OFFSET';

      SELECT control_value
        INTO v_brn_control_tmstmp
        FROM t_brn_control
        WHERE control_name = 'BRN_JOB_TIME';

      OPEN v_brn_cur ( v_brn_control_tmstmp, v_expiry_offset, v_recycle_offset, v_check_offset, v_current_tmstmp );
      LOOP

         FETCH v_brn_cur
         BULK COLLECT INTO brn_arr LIMIT v_limit;

          FORALL i IN 1..brn_arr.COUNT
             UPDATE t_basket_reference_master
                SET availability_flag = 1
              WHERE basket_reference_number = brn_arr(i);

         EXIT WHEN v_brn_cur%NOTFOUND;
      END LOOP;

      CLOSE v_brn_cur;

      UPDATE t_brn_control
         SET control_value = CURRENT_TIMESTAMP;

      COMMIT;

      pkg_logger.log_progress( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_progress_message  => 'BRN release process ended successfully'
                             , p_stage => 'BRN release'
                             , p_work_identifier => NULL
                             );

   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK;
         pkg_logger.log_error( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_app_error_message => 'Error occured in BRN release'
                             , p_oracle_message    => dbms_utility.format_error_stack || dbms_utility.format_error_backtrace
                             );

         pkg_logger.log_progress( p_package_name      => gc_package_name
                                , p_procedure_name    => v_procedure_name
                                , p_progress_message  => 'EXCEPTION: Error in BRN release process'
                                , p_stage => 'BRN release'
                                , p_work_identifier => NULL
                                );

         RAISE_APPLICATION_ERROR (-20001, 'Exception occured in ' || gc_package_name ||'.'||v_procedure_name||dbms_utility.format_error_stack || dbms_utility.format_error_backtrace);

   END brn_release;

END pkg_brn_release_manager;
/